﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace GeraPlanilha
{
	/// <summary>
	/// Pesquisa e recupera informações referente ao controle de estoque
	/// </summary>
	public class EstoqueDAO
	{
		private ImportaExcel excel = new ImportaExcel();
		private string conn ="Data Source=192.168.0.10;Initial Catalog=fabricaracao;Integrated Security=SSPI;";
        //private string conn =@"Data Source=.\SQLEXPRESS;Initial Catalog=fabricaracao;Integrated Security=SSPI;";
		
		public EstoqueDAO()
		{
		}

        private List<Estoque> RetornaRacoes(Estoque estoque)
        {
            string query = " SELECT X.DATA, X.DESCRICAO, ROUND( SUM(FABRICA), 2,0) AS FABRICA, ROUND( SUM(BALANCA),2,0) AS BALANCA, ROUND( SUM(FABRICA)- SUM(BALANCA),2,0) AS [TOTAL] FROM ( " +
                                  "  SELECT T.DATA, T.DESCRICAO, " +
                                  "  ISNULL(CASE WHEN FABRICA = 'FABRICA' THEN SUM(PESO) END, 0) AS FABRICA, " +
                                  "  ISNULL(CASE WHEN FABRICA = 'BALANCA' THEN SUM(PESO) END, 0) AS BALANCA " +
                                  "  FROM ( " +
                                  "    SELECT FABRICA, DESCRICAO, DATA, SUM(PESO) AS PESO " +
                                  "    FROM [VIEW_BATIDAS] " +
                                  "    WHERE DATA BETWEEN '"+FormataData( estoque.DataInicial)+"' AND '"+FormataData( estoque.DataFinal)+"'  " +
                                  "    GROUP BY FABRICA, DESCRICAO, DATA " +
                                  "    UNION ALL " +
                                  "    SELECT 'BALANCA', DESCRICAO, DATA,SUM(PESO)  AS PESO FROM VIEW_GUARDIAN " +
                                  "    WHERE DATA BETWEEN '"+FormataData( estoque.DataInicial)+"' AND '"+FormataData( estoque.DataFinal)+"'  " +
                                  "    GROUP BY DESCRICAO, DATA " +
                                    " ) AS T " +
                                   " WHERE T.DESCRICAO IS NOT NULL " +
                                   " GROUP BY T.DATA, T.DESCRICAO, T.FABRICA " +
                                   " ) AS X " +
                                   " GROUP BY X.DATA, X.DESCRICAO " +
                                   " ORDER BY X.DESCRICAO ASC, X.DATA ASC ";

            List<Estoque> estoques = new List<Estoque>();
            using (SqlConnection connection = new SqlConnection(conn))
            {
                using (SqlCommand cmd = new SqlCommand(query, connection))
                {
                    cmd.CommandType = CommandType.Text;
                    connection.Open();

                    SqlDataReader reader = cmd.ExecuteReader();
                    Estoque est;

                    while (reader.Read())
                    {
                        est = new Estoque();
                        est.Data = reader.GetDateTime(0);
                        est.Racao = reader.GetString(1);
                        est.Fabrica = reader.GetDouble(2);//Convert.ToDouble( reader.GetDouble(2).ToString().Replace(".", ",")); 
                        est.Balanca = -1 * reader.GetDouble(3);//Convert.ToDouble( .ToString().Replace(",", ".")); 
                        est.Total = reader.GetDouble(4) ;
                        estoques.Add(est);
                    }
                }
            }
            return estoques;
        }

        public List<Estoque> FormataRacoes(Estoque estoque)
        {
            Dictionary<string, DateTime> menoresDadas = DataInicialDeProducaoDaRacao(estoque);
            List<Estoque> racoes = RetornaRacoes(estoque);

            foreach (string racao in menoresDadas.Keys)
            {
                for (int i = 0; i < racoes.Count; i++)
                {
                    DateTime d = racoes[i].Data;
                    string r = racoes[i].Racao;
                    if (d == menoresDadas[racao] && r == racao)
                    {
                        racoes[i].Saldo = 0;
                    }
                    else
                    {
                        if (racoes[i].Racao == racao)
                        {
                            if (i - 1 >= 0)
                            {
                                double totalAnterior = racoes[i - 1].Total;
                                racoes[i].Saldo = totalAnterior;

                                racoes[i].Total = Convert.ToDouble( String.Format("{0:0.00}", (racoes[i].Saldo + racoes[i].Fabrica) + racoes[i].Balanca));

                            }
                        }
                    }
                }
            }
            return racoes;
        }
    
        private Dictionary<string, DateTime> DataInicialDeProducaoDaRacao(Estoque estoque)
        {
            Dictionary<string, DateTime> menorDataDaRacao = new Dictionary<string, DateTime>();
            string query = " SELECT BAT.DESCRICAO, MIN(BAT.DATA) " +
                                  " FROM  VIEW_BATIDAS BAT " +
                                  " WHERE BAT.DATA BETWEEN '" + FormataData(estoque.DataInicial) + "' AND '" + FormataData(estoque.DataFinal) + "'  " +
                                  " GROUP BY BAT.DESCRICAO " +
                                  " ORDER BY BAT.DESCRICAO ASC";

            using (SqlConnection connection = new SqlConnection(conn))
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                command.CommandText = query;

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                    menorDataDaRacao.Add(reader.GetString(0), reader.GetDateTime(1));

            }
            
            return menorDataDaRacao;
        }

		public void SalvaNoExcell(string path,  List<Estoque> estoques)
		{
			int i =2;
			TituloColunas();

			foreach(Estoque est in estoques)
			{
				excel.Escrever(i, 1, est.Data.ToShortDateString());
				excel.Escrever(i, 2, est.Racao);
				excel.Escrever(i, 3, est.Saldo.ToString());
				excel.Escrever(i, 4, est.Fabrica.ToString());
				excel.Escrever(i, 5, est.Balanca.ToString());
				excel.Escrever(i, 6, est.Total.ToString());
				i++;
			}
			excel.Salvar(path);
			excel.Sair();
		}
		
		private void TituloColunas()
		{
			excel.NovaPlanilha();
			excel.Escrever(1,1,"Data");
			excel.Escrever(1,2,"Ração");
			excel.Escrever(1,3,"Salto Inicial");
			excel.Escrever(1,4,"Produção Fabrica");
			excel.Escrever(1,5,"Envio Galpões");
			excel.Escrever(1,6,"Total");
		}
		
		private string FormataData(DateTime data)
		{
			return data.Year +"-"+ data.Month+"-"+data.Day+ " 00:00:00.000";
		}
	}
}
